﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook
{
    public class CashReceiptManager:ConnectionManager
    {
        #region Add/Update Cash Receipt
        public SqlException AddNewCashEntry(CashReceiptUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "INSERT INTO CASH_RECEIPT (DocNum, Reference, Remarks, DocType, ReceiptNumber, PostingDate, TINNumber, IsAccount, PayeeIndex, PayeeCode, PayeeName, AddressOrRemarks, " +
                    "CashAcctCode, CashAcctName, CashAmount, CheckAcctCode, CheckAcctName, CheckAmount, " +
                    "FundTrsfAcctCode, FundTrsfrAcctName, FundTrsfrBank, FundTrsfrReference, FundTrsfrDate, FundTrsfrAmount," +
                    "Total, DocStatus, DateCreated, CreatedBy, DateModified, ModifiedBy) VALUES " +
                    "(@DocNum, @Reference, @Remarks, @DocType, @ReceiptNumber, @PostingDate, @TINNumber, @IsAccount, @PayeeIndex, @PayeeCode, @PayeeName, @AddressOrRemarks, " +
                    "@CashAcctCode, @CashAcctName, @CashAmount, @CheckAcctCode, @CheckAcctName, @CheckAmount, " +
                    "@FundTrsfAcctCode, @FundTrsfrAcctName, @FundTrsfrBank, @FundTrsfrReference, @FundTrsfrDate, @FundTrsfrAmount," +
                    "@Total, @DocStatus, @DateCreated, @CreatedBy, @DateModified, @ModifiedBy)";




                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
                oCommand.Parameters.Add(new SqlParameter("@DocType", oUnit.DocumentType));
                oCommand.Parameters.Add(new SqlParameter("@ReceiptNumber", oUnit.ReceiptNumber));
                oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
                oCommand.Parameters.Add(new SqlParameter("@TINNumber", oUnit.TINNumber));
                oCommand.Parameters.Add(new SqlParameter("@IsAccount", oUnit.IsAccount));
                oCommand.Parameters.Add(new SqlParameter("@PayeeIndex", oUnit.PayeeIndex));
                oCommand.Parameters.Add(new SqlParameter("@PayeeCode", oUnit.CardCode));
                oCommand.Parameters.Add(new SqlParameter("@PayeeName", oUnit.CardName));
                oCommand.Parameters.Add(new SqlParameter("@AddressOrRemarks", oUnit.Address));

                //CashAcctCode	nvarchar(50)	Checked
                //CashAcctName	nvarchar(250)	Checked
                //CashAmount	numeric(18, 2)	Checked


                #region Cash Entries
                oCommand.Parameters.Add(new SqlParameter("@CashAcctCode", oUnit.CashGLAcctCode));
                oCommand.Parameters.Add(new SqlParameter("@CashAcctName", oUnit.CashGLAcctName));
                oCommand.Parameters.Add(new SqlParameter("@CashAmount", oUnit.CashAmount));
                #endregion


                //CheckAcctCode	nvarchar(50)	Checked
                //CheckAcctName	nvarchar(50)	Checked
                //CheckBankName	nvarchar(50)	Checked
                //CheckNumber	nvarchar(50)	Checked
                //CheckDate	nvarchar(50)	Checked
                //CheckAmount	numeric(18, 2)	Checked
                //CheckStatus	nvarchar(50)	Checked

                #region Check Entries
                oCommand.Parameters.Add(new SqlParameter("@CheckAcctCode", oUnit.CheckGLAcctCode));
                oCommand.Parameters.Add(new SqlParameter("@CheckAcctName", oUnit.CheckGLAcctName));
                //oCommand.Parameters.Add(new SqlParameter("@CheckBankName", oUnit.CheckBankName));
                //oCommand.Parameters.Add(new SqlParameter("@CheckNumber", oUnit.CheckNumber));
                //oCommand.Parameters.Add(new SqlParameter("@CheckDate", oUnit.CheckDate));
                oCommand.Parameters.Add(new SqlParameter("@CheckAmount", oUnit.CheckAmount));
                //oCommand.Parameters.Add(new SqlParameter("@CheckStatus", oUnit.CheckStatus));
                #endregion


                //FundTrsfAcctCode	nvarchar(50)	Checked
                //FundTrsfrAcctName	nvarchar(50)	Checked
                //FundTrsfrBank	nvarchar(50)	Checked
                //FundTrsfrReference	nvarchar(50)	Checked
                //FundTrsfrDate	datetime	Checked
                //FundTrsfrAmount	numeric(18, 2)	Checked

                #region Fund Transfer
                oCommand.Parameters.Add(new SqlParameter("@FundTrsfAcctCode", oUnit.FundTransferAcctCode));
                oCommand.Parameters.Add(new SqlParameter("@FundTrsfrAcctName", oUnit.FundTransferAcctName));
                oCommand.Parameters.Add(new SqlParameter("@FundTrsfrBank", oUnit.FundTransferBank));
                oCommand.Parameters.Add(new SqlParameter("@FundTrsfrReference", oUnit.FundTransferReference));
                oCommand.Parameters.Add(new SqlParameter("@FundTrsfrDate", oUnit.FundTransferDate));
                oCommand.Parameters.Add(new SqlParameter("@FundTrsfrAmount", oUnit.FundTransferAmount));
                #endregion

                oCommand.Parameters.Add(new SqlParameter("@Total", oUnit.TotalAmount));
                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
                oCommand.Parameters.Add(new SqlParameter("@DateCreated", oUnit.DateCreated));
                oCommand.Parameters.Add(new SqlParameter("@CreatedBy", oUnit.CreatedBy));
                oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));

                oCommand.ExecuteNonQuery();


                #region Check Line Items

                string _INSERT_CHECK_ENTRIES_QUERY = "INSERT INTO CASH_RECEIPT_CHECKS (DocNum, CheckAcctCode, CheckAcctName, BankName, CheckNumber, CheckAmount, CheckDate, CheckStatus) VALUES" +
                                                                                                                         " (@DocNum, @CheckAcctCode, @CheckAcctName, @BankName, @CheckNumber, @CheckAmount, @CheckDate, @CheckStatus)";
                foreach (DataRow oRow in oUnit.CheckLineItems.Rows)
                {
                    try
                    {
                        oCommand = new SqlCommand();
                        oCommand.Connection = this.Connection;
                        oCommand.CommandText = _INSERT_CHECK_ENTRIES_QUERY;

                        oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                        oCommand.Parameters.Add(new SqlParameter("@CheckAcctCode", oUnit.CheckGLAcctCode));
                        oCommand.Parameters.Add(new SqlParameter("@CheckAcctName", oUnit.CheckGLAcctName));
                        oCommand.Parameters.Add(new SqlParameter("@BankName", oRow["BankName"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@CheckNumber", Convert.ToString(oRow["CheckNumber"])));
                        oCommand.Parameters.Add(new SqlParameter("@CheckAmount", Convert.ToDouble(oRow["CheckAmount"])));
                        oCommand.Parameters.Add(new SqlParameter("@CheckDate", Convert.ToDateTime(oRow["CheckDate"])));
                        oCommand.Parameters.Add(new SqlParameter("@CheckStatus", Convert.ToString(oRow["CheckStatus"])));

                        oCommand.ExecuteNonQuery();
                    }
                    catch { }


                }

                #endregion


                #region Applied Deductions and Expenses

                string _INSERT_DEDUCTION_AND_EXPENSES_QUERY = "INSERT INTO CASH_RECEIPT_DEDUCTIONS (DocNum, Particulars, AcctCode, AcctName, Amount) VALUES" +
                                                                                                                         " (@DocNum, @Particulars, @AcctCode, @AcctName, @Amount)";
                foreach (DataRow oRow in oUnit.AppliedDeductionAndExpenses.Rows)
                {
                    try
                    {
                        oCommand = new SqlCommand();
                        oCommand.Connection = this.Connection;
                        oCommand.CommandText = _INSERT_DEDUCTION_AND_EXPENSES_QUERY;

                        oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                        oCommand.Parameters.Add(new SqlParameter("@Particulars", oRow["Particulars"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@AcctCode", oRow["AcctCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@AcctName", oRow["AcctName"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Amount", Convert.ToDouble(oRow["Amount"])));

                        oCommand.ExecuteNonQuery();
                    }
                    catch { }


                }

                #endregion

                #region Applied Invoices

                string _INSERT_INVOICES_QUERY = "INSERT INTO CASH_RECEIPT_INVOICES (DocType, DocNum, InvoiceNumber, Reference, Amount, AppliedAmount) VALUES" +
                                                                                                                         " (@DocType, @DocNum, @InvoiceNumber, @Reference, @Amount, @AppliedAmount)";
                foreach (DataRow oRow in oUnit.AppliedSalesInvoice.Rows)
                {
                    oCommand = new SqlCommand();
                    oCommand.Connection = this.Connection;
                    oCommand.CommandText = _INSERT_INVOICES_QUERY;

                    oCommand.Parameters.Add(new SqlParameter("@DocType", oRow["DocType"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                    oCommand.Parameters.Add(new SqlParameter("@InvoiceNumber", oRow["InvoiceNumber"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Reference", oRow["Reference"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@AppliedAmount", oRow["AppliedAmount"].ToString()));
                    oCommand.ExecuteNonQuery();


                }

                #endregion

                #region Applied Sales Returns

                string _INSERT_RETURN_QUERY = "INSERT INTO CASH_RECEIPT_RETURNS (DocNum, ReturnNo, Amount, AppliedAmount) VALUES" +
                                                                                                                         " (@DocNum, @ReturnNo, @Amount, @AppliedAmount)";
                foreach (DataRow oRow in oUnit.AppliedSalesReturn.Rows)
                {
                    try
                    {
                        oCommand = new SqlCommand();
                        oCommand.Connection = this.Connection;
                        oCommand.CommandText = _INSERT_RETURN_QUERY;

                        oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                        oCommand.Parameters.Add(new SqlParameter("@ReturnNo", oRow["ReturnNo"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@AppliedAmount", oRow["AppliedAmount"].ToString()));
                        oCommand.ExecuteNonQuery();
                    }
                    catch { }


                }

                #endregion

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException UpdateCashEntry(CashReceiptUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "UPDATE CASH_RECEIPT SET Reference=@Reference, Remarks=@Remarks, DocType=@DocType, ReceiptNumber=@ReceiptNumber, PostingDate=@PostingDate, TINNumber=@TINNumber, IsAccount=@IsAccount, PayeeIndex = @PayeeIndex,PayeeCode=@PayeeCode, PayeeName=@PayeeName, AddressOrRemarks=@AddressOrRemarks, " +
                    "CashAcctCode=@CashAcctCode, CashAcctName=@CashAcctName, CashAmount=@CashAmount, CheckAcctCode=@CheckAcctCode, CheckAcctName=@CheckAcctName, CheckAmount=@CheckAmount, " +
                    "FundTrsfAcctCode=@FundTrsfAcctCode, FundTrsfrAcctName=@FundTrsfrAcctName, FundTrsfrBank=@FundTrsfrBank, FundTrsfrReference=@FundTrsfrReference, FundTrsfrDate=@FundTrsfrDate, FundTrsfrAmount=@FundTrsfrAmount, Total=@Total," +
                    "DocStatus=@DocStatus, DateModified=@DateModified, ModifiedBy=@ModifiedBy WHERE DocNum=@DocNum";

                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@DocType", oUnit.DocumentType));
                oCommand.Parameters.Add(new SqlParameter("@ReceiptNumber", oUnit.ReceiptNumber));
                oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
                oCommand.Parameters.Add(new SqlParameter("@TINNumber", oUnit.TINNumber));
                oCommand.Parameters.Add(new SqlParameter("@IsAccount", oUnit.IsAccount));
                oCommand.Parameters.Add(new SqlParameter("@PayeeIndex", oUnit.PayeeIndex));
                oCommand.Parameters.Add(new SqlParameter("@PayeeCode", oUnit.CardCode));
                oCommand.Parameters.Add(new SqlParameter("@PayeeName", oUnit.CardName));
                oCommand.Parameters.Add(new SqlParameter("@AddressOrRemarks", oUnit.Address));






                //CashAcctCode	nvarchar(50)	Checked
                //CashAcctName	nvarchar(250)	Checked
                //CashAmount	numeric(18, 2)	Checked


                #region Cash Entries
                oCommand.Parameters.Add(new SqlParameter("@CashAcctCode", oUnit.CashGLAcctCode));
                oCommand.Parameters.Add(new SqlParameter("@CashAcctName", oUnit.CashGLAcctName));
                oCommand.Parameters.Add(new SqlParameter("@CashAmount", oUnit.CashAmount));
                #endregion


                //CheckAcctCode	nvarchar(50)	Checked
                //CheckAcctName	nvarchar(50)	Checked
                //CheckBankName	nvarchar(50)	Checked
                //CheckNumber	nvarchar(50)	Checked
                //CheckDate	nvarchar(50)	Checked
                //CheckAmount	numeric(18, 2)	Checked
                //CheckStatus	nvarchar(50)	Checked

                #region Check Entries
                oCommand.Parameters.Add(new SqlParameter("@CheckAcctCode", oUnit.CheckGLAcctCode));
                oCommand.Parameters.Add(new SqlParameter("@CheckAcctName", oUnit.CheckGLAcctName));
                oCommand.Parameters.Add(new SqlParameter("@CheckAmount", oUnit.CheckAmount));
                #endregion


                //FundTrsfAcctCode	nvarchar(50)	Checked
                //FundTrsfrAcctName	nvarchar(50)	Checked
                //FundTrsfrBank	nvarchar(50)	Checked
                //FundTrsfrReference	nvarchar(50)	Checked
                //FundTrsfrDate	datetime	Checked
                //FundTrsfrAmount	numeric(18, 2)	Checked

                #region Fund Transfer
                oCommand.Parameters.Add(new SqlParameter("@FundTrsfAcctCode", oUnit.FundTransferAcctCode));
                oCommand.Parameters.Add(new SqlParameter("@FundTrsfrAcctName", oUnit.FundTransferAcctName));
                oCommand.Parameters.Add(new SqlParameter("@FundTrsfrBank", oUnit.FundTransferBank));
                oCommand.Parameters.Add(new SqlParameter("@FundTrsfrReference", oUnit.FundTransferReference));
                oCommand.Parameters.Add(new SqlParameter("@FundTrsfrDate", oUnit.FundTransferDate));
                oCommand.Parameters.Add(new SqlParameter("@FundTrsfrAmount", oUnit.FundTransferAmount));
                #endregion

                oCommand.Parameters.Add(new SqlParameter("@Total", oUnit.TotalAmount));

                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
                oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));

                oCommand.ExecuteNonQuery();



                #region Check Receipts


                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "DELETE FROM CASH_RECEIPT_CHECKS WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.ExecuteNonQuery();

                string _INSERT_CHECK_ENTRIES_QUERY = "INSERT INTO CASH_RECEIPT_CHECKS (DocNum, CheckAcctCode, CheckAcctName, BankName, CheckNumber, CheckAmount, CheckDate, CheckStatus) VALUES" +
                                                                                                                        " (@DocNum, @CheckAcctCode, @CheckAcctName, @BankName, @CheckNumber, @CheckAmount, @CheckDate, @CheckStatus)";
                foreach (DataRow oRow in oUnit.CheckLineItems.Rows)
                {
                    oCommand = new SqlCommand();
                    oCommand.Connection = this.Connection;
                    oCommand.CommandText = _INSERT_CHECK_ENTRIES_QUERY;

                    oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                    oCommand.Parameters.Add(new SqlParameter("@CheckAcctCode", oUnit.CheckGLAcctCode));
                    oCommand.Parameters.Add(new SqlParameter("@CheckAcctName", oUnit.CheckGLAcctName));
                    oCommand.Parameters.Add(new SqlParameter("@BankName", oRow["BankName"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@CheckNumber", Convert.ToString(oRow["CheckNumber"])));
                    oCommand.Parameters.Add(new SqlParameter("@CheckAmount", Convert.ToDouble(oRow["CheckAmount"])));
                    oCommand.Parameters.Add(new SqlParameter("@CheckDate", Convert.ToDateTime(oRow["CheckDate"])));
                    oCommand.Parameters.Add(new SqlParameter("@CheckStatus", Convert.ToString(oRow["CheckStatus"])));

                    oCommand.ExecuteNonQuery();


                }

                #endregion


                #region Applied Deductions and Expenses


                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "DELETE FROM CASH_RECEIPT_DEDUCTIONS WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.ExecuteNonQuery();

                string _INSERT_DEDUCTION_AND_EXPENSES_QUERY = "INSERT INTO CASH_RECEIPT_DEDUCTIONS (DocNum, Particulars, AcctCode, AcctName, Amount) VALUES" +
                                                                                                                         " (@DocNum, @Particulars, @AcctCode, @AcctName, @Amount)";
                foreach (DataRow oRow in oUnit.AppliedDeductionAndExpenses.Rows)
                {
                    if (oRow.RowState != DataRowState.Deleted)
                    {
                      
                        oCommand = new SqlCommand();
                        oCommand.Connection = this.Connection;
                        oCommand.CommandText = _INSERT_DEDUCTION_AND_EXPENSES_QUERY;

                        oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                        oCommand.Parameters.Add(new SqlParameter("@Particulars", oRow["Particulars"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@AcctCode", oRow["AcctCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@AcctName", oRow["AcctName"].ToString()));
                        if (oRow["Amount"] != DBNull.Value)
                        {
                            oCommand.Parameters.Add(new SqlParameter("@Amount", Convert.ToDouble(oRow["Amount"])));
                        }
                        else
                        {
                            oCommand.Parameters.Add(new SqlParameter("@Amount", 0));
                        }

                        oCommand.ExecuteNonQuery();
                        
                    }

                }

                #endregion

                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "DELETE FROM CASH_RECEIPT_INVOICES WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.ExecuteNonQuery();

                #region Applied Invoices

                string _INSERT_INVOICES_QUERY = "INSERT INTO CASH_RECEIPT_INVOICES (DocType, DocNum, InvoiceNumber, Reference, Amount, AppliedAmount) VALUES" +
                                                                                                                         " (@DocType, @DocNum, @InvoiceNumber, @Reference, @Amount, @AppliedAmount)";
                foreach (DataRow oRow in oUnit.AppliedSalesInvoice.Rows)
                {
                    if (oRow.RowState != DataRowState.Deleted)
                    {
                        oCommand = new SqlCommand();
                        oCommand.Connection = this.Connection;
                        oCommand.CommandText = _INSERT_INVOICES_QUERY;

                        oCommand.Parameters.Add(new SqlParameter("@DocType", oRow["DocType"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                        oCommand.Parameters.Add(new SqlParameter("@InvoiceNumber", oRow["InvoiceNumber"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Reference", oRow["Reference"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@AppliedAmount", oRow["AppliedAmount"].ToString()));
                        oCommand.ExecuteNonQuery();
                        
                    }


                }

                #endregion



                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "DELETE FROM CASH_RECEIPT_RETURNS WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.ExecuteNonQuery();

                #region Applied Sales Returns

                string _INSERT_RETURN_QUERY = "INSERT INTO CASH_RECEIPT_RETURNS (DocNum, ReturnNo, Amount, AppliedAmount) VALUES" +
                                                                                                                         " (@DocNum, @ReturnNo, @Amount, @AppliedAmount)";
                foreach (DataRow oRow in oUnit.AppliedSalesReturn.Rows)
                {
                    if (oRow.RowState != DataRowState.Deleted)
                    {  
                        oCommand = new SqlCommand();
                        oCommand.Connection = this.Connection;
                        oCommand.CommandText = _INSERT_RETURN_QUERY;

                        oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                        oCommand.Parameters.Add(new SqlParameter("@ReturnNo", oRow["ReturnNo"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Amount", Convert.ToDouble(oRow["Amount"])));
                        oCommand.Parameters.Add(new SqlParameter("@AppliedAmount", Convert.ToDouble(oRow["AppliedAmount"].ToString())));
                        oCommand.ExecuteNonQuery();
                      
                    }


                }

                #endregion

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }

        }

        #endregion


        public SqlException ClearCheck(string PaymentNo, string BankName, string CheckNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "UPDATE CASH_RECEIPT_CHECKS SET CheckStatus='Cleared' WHERE DocNum=@DocNum AND BankName=@BankName AND CheckNumber=@CheckNumber";

                oCommand.Parameters.Add(new SqlParameter("DocNum", PaymentNo));
                oCommand.Parameters.Add(new SqlParameter("BankName", BankName));
                oCommand.Parameters.Add(new SqlParameter("CheckNumber", CheckNumber));

                oCommand.ExecuteNonQuery();
                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }

        }

        #region Get/Browse Cash Receipt

        public DataTable GetCheckForClearing(DateTime _ASOF)        
        {
            string _SELECT_SQL = "EXEC SP_CHECK_CLEARING @CHECKDATE";

            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.Text;
            oCommand.CommandText = _SELECT_SQL;

            oCommand.Parameters.Add(new SqlParameter("@CHECKDATE", _ASOF));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable GetCashEntry(string DocNum)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection =this.Connection;
            oCommand.CommandText = "SELECT * FROM CASH_RECEIPT WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", DocNum));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowseCashEntryByDocument(string _DocNum)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            _DocNum = '%' + _DocNum.Trim() + '%';

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM VW_CASHRECEIPT_DOCUMENT WHERE DocNum LIKE @DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocNum));

            
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowseCashEntryByReceiptNumner(string _DocumentType, string _ReceiptNumber)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            _ReceiptNumber = '%' + _ReceiptNumber.Trim() + '%';

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM VW_CASHRECEIPT_DOCUMENT WHERE DocType=@DocType AND ReceiptNumber LIKE @ReceiptNumber";
            oCommand.Parameters.Add(new SqlParameter("@ReceiptNumber", _ReceiptNumber));
            oCommand.Parameters.Add(new SqlParameter("@DocType", _DocumentType));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowseCashEntryByCardName(string _CardName)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            _CardName = '%' + _CardName.Trim() + '%';

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM VW_CASHRECEIPT_DOCUMENT WHERE PayeeName LIKE @CardName";
            oCommand.Parameters.Add(new SqlParameter("@CardName", _CardName));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable GetCheckPayments(string DocNum)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM CASH_RECEIPT_CHECKS WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", DocNum));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable GetAppliedSalesReturn(string DocNum)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM CASH_RECEIPT_RETURNS WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", DocNum));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable GetAppliedInvoices(string DocNum)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM CASH_RECEIPT_INVOICES WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", DocNum));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable GetAppliedDeduction(string DocNum)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM CASH_RECEIPT_DEDUCTIONS WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", DocNum));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }
        #endregion

        #region Status
        public SqlException PostDocument(string _DOCUMENT_NUMBER)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "UPDATE CASH_RECEIPT SET DOCSTATUS='Posted' WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCUMENT_NUMBER));
                oCommand.ExecuteNonQuery();
                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }

        }

        public SqlException CloseDocument(string _DOCUMENT_NUMBER)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE CASH_RECEIPT SET DOCSTATUS='Closed' WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCUMENT_NUMBER));
                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException CancelDocument(string _DOCUMENT_NUMBER)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "UPDATE CASH_RECEIPT SET DOCSTATUS='Canceled' WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCUMENT_NUMBER));
                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }
        #endregion
    }
}
